﻿Option Strict On

Imports Microsoft.VisualBasic
Imports System.Data.OleDb

Public Class ForumDAO
    Dim connStr As String = ConfigurationManager.ConnectionStrings("ungvarberg").ConnectionString


    'hämtar alla forumsinlägg
    Public Function GetAllForum() As List(Of Forum)
        Dim list As New List(Of Forum)

        Dim sql As String = "SELECT tblForum.NewsID, Header, Texten, DateNews, [Name], [Email] FROM tblForum WHERE Confirm = true ORDER BY DateNews DESC "

        Dim connection As New OleDbConnection(connStr)
        Dim command As New OleDbCommand(sql, connection)

        Dim Forum As New Forum

        Try
            connection.Open()
            Dim reader As OleDbDataReader = command.ExecuteReader()

            While reader.Read()
                Forum = New Forum

                Forum.Header = reader("Header").ToString
                Forum.Texten = reader("Texten").ToString
                Forum.Name = reader("Name").ToString
                Forum.Email = reader("Email").ToString
                Forum.DateNews = CType(reader("DateNews"), DateTime)
                Forum.NewsID = CType(reader("NewsID"), Integer)

                list.Add(Forum)
            End While

        Catch ex As Exception
            Throw ex
        Finally
            connection.Close()
            connection.Dispose()
        End Try

        Return list
    End Function

    'skapar nytt inlägg i forumet
    Public Sub createNewMessage(ByVal forum As Forum)
        Dim sql As String = "INSERT INTO tblForum(Header, Texten, Name, Email, Confirm)" & _
        " values(@Header,@Texten,@Name,@Email,false);"

        Dim connection As New OleDbConnection(connStr)

        Dim command As New OleDbCommand(sql, connection)

        command.Parameters.AddWithValue("@Header", forum.Header)
        command.Parameters.AddWithValue("@Texten", forum.Texten)
        command.Parameters.AddWithValue("@Name", forum.Name)
        command.Parameters.AddWithValue("@Email", forum.Email)

        '  command.Parameters.AddWithValue("@datum", news.datum)

        Try
            connection.Open()
            command.ExecuteNonQuery()

        Catch ex As Exception
            Throw ex
        Finally
            connection.Close()
            connection.Dispose()
        End Try

    End Sub

    'hämtar alla forumsinlägg
    Public Function GetAllForumAdmin() As List(Of Forum)
        Dim list As New List(Of Forum)

        Dim sql As String = "SELECT tblForum.NewsID, Header, Texten, DateNews, [Name], [Email] FROM tblForum WHERE Confirm = false ORDER BY DateNews DESC "

        Dim connection As New OleDbConnection(connStr)
        Dim command As New OleDbCommand(sql, connection)

        Dim Forum As New Forum

        Try
            connection.Open()
            Dim reader As OleDbDataReader = command.ExecuteReader()

            While reader.Read()
                Forum = New Forum

                Forum.Header = reader("Header").ToString
                Forum.Texten = reader("Texten").ToString
                Forum.Name = reader("Name").ToString
                Forum.Email = reader("Email").ToString
                Forum.DateNews = CType(reader("DateNews"), DateTime)
                Forum.NewsID = CType(reader("NewsID"), Integer)

                list.Add(Forum)
            End While

        Catch ex As Exception
            Throw ex
        Finally
            connection.Close()
            connection.Dispose()
        End Try

        Return list
    End Function


    'Ändrar inlägg till att kunna publiceras
    Public Sub UpdateMessage(ByVal f As Forum)
        Dim sql As String = "Update tblForum set Confirm = True WHERE NewsID = ? "

        Dim connection As New OleDbConnection(connStr)
        Dim command As New OleDbCommand(sql, connection)

        command.Parameters.AddWithValue("?", f.NewsID)


        Try
            connection.Open()
            command.ExecuteNonQuery()
        Catch ex As Exception
            Throw ex
        Finally
            connection.Close()
            connection.Dispose()

        End Try
    End Sub


    'Raderar inlägg på adminsidan
    Public Sub DeleteMessage(ByVal id As Integer)
        Dim sql As String = "Delete Header, Texten from [tblForum] where NewsID = ?"

        Dim connection As New OleDbConnection(connStr)
        Dim command As New OleDbCommand(sql, connection)

        command.Parameters.AddWithValue("?NewsID", id)

        Try
            connection.Open()
            command.ExecuteNonQuery()
        Catch ex As Exception
            Throw ex
        Finally
            connection.Close()
            connection.Dispose()

        End Try
    End Sub

End Class
